<?php

namespace App\V3\V3_2_7\Services;

use App\Services\CommonService;
use App\V3\V3_2_7\Models\BarterModel;
use App\V3\V3_2_7\Models\LogInStorageModel;
use App\V3\V3_2_7\Models\LogOutStorageModel;
use App\V3\V3_2_7\Models\MaterielModel;
use App\V3\V3_2_7\Models\MaterielTypeModel;
use App\V3\V3_2_7\Models\SellGoodsDetail;
use App\V3\V3_2_7\Models\SellModel;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Arr;

class SellReportService
{
    /**
     * 根据物料名维度，统计销售报表
     * @param $data
     * @return void
     */
    public static function materielNameList($mainId,$data,$export=false)
    {
        $result         = ['pageData'=>[],'totalData'=>[]]; //返回结果
        $startTime      = isset($data['start_time']) ? self::dateToTime($data['start_time']) : ''; //开始时间
        $endTime        = isset($data['end_time']) ? self::dateToTime($data['end_time'],1) : ''; //结束时间 + 1天
        $materielName   = isset($data['materiel_name']) ? $data['materiel_name'] : ''; //物料名称
        $sellPriceSort  = isset($data['sell_price_sort']) ? strtolower($data['sell_price_sort']) : ''; //销售额排序，asc=升序，desc=降序，默认为空
        $sellProfitSort = isset($data['sell_profit_sort']) ? strtolower($data['sell_profit_sort']) : ''; //销售利润排序，asc=升序，desc=降序，默认为空
        //第一步，如果2个时间都为空，数据为空
//        if($startTime == '' && $endTime == ''){
//            return CommonService::success('数据为空',$result);
//        }

        //第二步，从log表里分页查询出结果集
        $query = LogOutStorageModel::query();
        $query->from('log_outstorage AS lo')
            ->leftJoin("materiel_type AS mt","lo.materiel_type_id","=","mt.id")
            ->select(
                ["lo.materiel_type_id","lo.materiel_name",
                    "mt.cost","mt.selling_price","mt.materiel_no","mt.type_name",
                    DB::raw("SUM( s_lo.operate_num ) AS row_operate_num"),
                    DB::raw("SUM( (s_lo.sell_price - s_mt.cost) * s_lo.operate_num ) AS row_profit_price"),
                    DB::raw("SUM( s_lo.sell_price * s_lo.operate_num ) AS row_sell_price")
                ]
            );
        if(!empty($data['user_id'])){
            $query->where("lo.user_id",$data['user_id']);
        }else{
            $query->where("lo.main_id",$mainId);
        }
        $query->whereIn("lo.all_id_type",[2,6])
            ->where("lo.is_cancel",0)
            ->where("lo.delete_flag",0)
            ->groupBy("lo.materiel_type_id");
        //出售销售时间
        if($startTime != ''){
            $query->where("lo.out_storage_time",">=",$startTime);
        }
        if($endTime != ''){
            $query->where("lo.out_storage_time","<",$endTime);
        }

        //搜索物料名称
        $materielSearchIdList = [0];
        if($materielName != ''){
            //从物料表里查 id 集合
            $materielSearchList     = MaterielTypeModel::query()->where('main_id',$mainId)->where('materiel_name','like',"{$materielName}%")->get();
            $materielSearchArr      = $materielSearchList->toArray();
            $materielSearchIdList   = Arr::pluck($materielSearchArr,'id');
            $query->whereIn("lo.materiel_type_id",$materielSearchIdList);
        }

        //销售价排序
        if($sellPriceSort == 'asc'){
            $query->orderBy("row_sell_price","asc");
        }elseif ($sellPriceSort == 'desc'){
            $query->orderBy("row_sell_price","desc");
        }

        //利润排序
        if($sellProfitSort == 'asc'){
            $query->orderBy("row_profit_price","asc");
        }elseif ($sellProfitSort == 'desc'){
            $query->orderBy("row_profit_price","desc");
        }

        //默认出库时间倒序
        $query->orderBy("lo.out_storage_time","desc");
        if($export){
            //导出，获取所有
            $materielList = $query->get();
            if(!empty($materielList->toArray())){
                $materielList = $materielList->toArray();
                foreach ($materielList as &$mVal){
                    $mVal['selling_price'] = sprintf("%.6f", ($mVal['row_sell_price']/$mVal['row_operate_num']));
                }
            }
        }else{

            $materielList = $query->paginate($data['pageSize'],['*'], '',$data['currentPage']);
            if(!empty($materielList->toArray())){
                $materielList = $materielList->toArray();
                if(!empty($materielList['data'])){
                    $mData = $materielList['data'];
                    foreach ($mData as &$mVal){
                        if(empty($mVal['row_sell_price']) ||empty($mVal['row_operate_num'])){
                            $mVal['selling_price'] = 0;
                        }else{
                            $mVal['selling_price'] = sprintf("%.6f", ($mVal['row_sell_price']/$mVal['row_operate_num']));

                        }
                    }
                    $materielList['data'] = $mData;
                }
            }


        }


        //第三步，计算统计总数
        $queryTotal = LogOutStorageModel::query();
        $queryTotal->from('log_outstorage AS lo')
            ->leftJoin("materiel_type AS mt","lo.materiel_type_id","=","mt.id")
            ->select( [
                DB::raw("SUM( s_mt.cost * s_lo.operate_num ) AS total_cost_price"),
                DB::raw("SUM( s_mt.selling_price * s_lo.operate_num ) AS total_sell_price")
            ] )
            ->where("lo.main_id",$mainId)
            ->whereIn("lo.all_id_type",[2,6])
            ->where("lo.is_cancel",0)
            ->where("lo.delete_flag",0)
            ->where("mt.main_id",$mainId);

        if($startTime != ''){
            $queryTotal->where("lo.out_storage_time",">=",$startTime);
        }
        if($endTime != ''){
            $queryTotal->where("lo.out_storage_time","<",$endTime);
        }

        if($materielName != ''){
            //从物料表里查 id 集合
            $query->whereIn("lo.materiel_type_id",$materielSearchIdList);
        }

        $totalList    = $queryTotal->first();

        //返回结果
        $result['pageData'] = $materielList;
        $result['totalData']= $totalList->toArray();

        if($export){
            return $result;
        }else{
            return CommonService::success('查询成功',$result);
        }
    }

    /**
     * 根据物料信息，查询销售报表
     * @param $data
     * @return void
     */
    public static function barterGoodsStatement($mainId,$data,$export=false)
    {
        $result         = ['pageData'=>[],'totalData'=>[]]; //返回结果
        $startTime      = isset($data['start_time']) ? self::dateToTime($data['start_time']) : ''; //开始时间
        $endTime        = isset($data['end_time']) ? self::dateToTime($data['end_time'],1) : ''; //结束时间 + 1天
        $sellNumSort  = isset($data['sell_num_sort']) ? strtolower($data['sell_num_sort']) : ''; //销售数量排序，asc=升序，desc=降序，默认为空
        $sellDebtSort  = isset($data['sell_debt_sort']) ? strtolower($data['sell_debt_sort']) : ''; //销售欠款排序，asc=升序，desc=降序，默认为空
        $sellList = [];
        //第二步，从log表里分页查询出结果集
        $query = LogOutStorageModel::query();
        $query->from('log_outstorage AS lo');
        if($data['type'] ==2){
            $query->leftJoin("sell AS s","lo.all_id","=","s.id");
            $query->leftJoin('sell_pay_record as sp', 's.id', '=', 'sp.sell_id');
        }else{
            $query->leftJoin("barter AS b","lo.all_id","=","b.id");
            $query->leftJoin('barter_pay_record as bp', 'b.id', '=', 'bp.barter_id');
        }

        $query->select(
            "lo.all_id as id",
            "order_no",
            "lo.operate_num",
            "lo.materiel_mean_cost",
            "lo.sell_price",
            "lo.out_storage_time",
            "lo.materiel_type_id",
            "customer_name",
            "final_money",
            DB::raw('final_money-SUM(expend) as debt')
        )
            ->where("lo.main_id",$mainId)
            ->where("lo.all_id_type",$data['type'])
            ->where("lo.is_cancel",0)
            ->where("lo.delete_flag",0)
            ->where("lo.materiel_type_id",$data['goods_id'])
            ->groupBy("lo.all_id");

        //出售销售时间
        if($startTime != ''){
            $query->where("lo.out_storage_time",">=",$startTime);
        }
        if($endTime != ''){
            $query->where("lo.out_storage_time","<",$endTime);
        }

        //销售价排序
        if($sellNumSort == 'asc'){
            $query->orderBy("operate_num","asc");
        }elseif ($sellNumSort == 'desc'){
            $query->orderBy("operate_num","desc");
        }
        //默认出库时间倒序
        $query->orderBy("lo.out_storage_time","desc");
        if($export){
            //导出，获取所有
            $materielList = $query->get();
            if(!empty($materielList)){
                $sellList = $materielList->toArray();
            }
        }else{
            $materielList = $query->paginate($data['pageSize'],['*'], '',$data['currentPage']);
            if(!empty($materielList->toArray()['data'])){
                $sellList = $materielList->toArray()['data'];
            }
        }
        if(!empty($sellList)){
            foreach ($sellList as &$mVal){
                if(is_null($mVal['debt'])){
                    $mVal['debt'] = $mVal['final_money'];
                }elseif(is_numeric($mVal['debt']) && $mVal['debt']<= 0){
                    $mVal['debt'] = 0;
                }
            }

            if(!empty($sellDebtSort)){//数量排序
                $paramSort = array_column($sellList,'debt');
                $sort = $sellDebtSort=='desc'?SORT_DESC:SORT_ASC;
                array_multisort($paramSort,$sort,$sellList);
            }
        }
        $materielList = $materielList->toArray();
        if(isset($materielList['data'])){
            $materielList['data'] = $sellList;
        }else{
            $materielList= $sellList;
        }
        //返回结果
        $result['pageData'] = $materielList;

        if($export){
            return $result;
        }else{
            return CommonService::success('查询成功',$result);
        }
    }
    /**
     * 根据物料信息，查询销售报表
     * @param $data
     * @return void
     */
    public static function sellGoodsStatement($mainId,$data,$export=false)
    {
        $result         = ['pageData'=>[],'totalData'=>[]]; //返回结果
        $startTime      = isset($data['start_time']) ? self::dateToTime($data['start_time']) : ''; //开始时间
        $endTime        = isset($data['end_time']) ? self::dateToTime($data['end_time'],1) : ''; //结束时间 + 1天
        $sellNumSort  = isset($data['sell_num_sort']) ? strtolower($data['sell_num_sort']) : ''; //销售数量排序，asc=升序，desc=降序，默认为空
        $sellDebtSort  = isset($data['sell_debt_sort']) ? strtolower($data['sell_debt_sort']) : ''; //销售欠款排序，asc=升序，desc=降序，默认为空
        $sellList = [];
        //第二步，从log表里分页查询出结果集
        $query = LogOutStorageModel::query();
        $query->from('log_outstorage AS lo');
        if($data['type'] ==2){
            $query->leftJoin("sell AS s","lo.all_id","=","s.id");
            $query->leftJoin('sell_pay_record as sp', 's.id', '=', 'sp.sell_id');
        }else{
            $query->leftJoin("barter AS b","lo.all_id","=","b.id");
            $query->leftJoin('barter_pay_record as bp', 'b.id', '=', 'bp.barter_id');
        }

        $query->select(
            "lo.all_id as id",
            "order_no",
            "lo.operate_num",
            "lo.materiel_mean_cost",
            "lo.sell_price",
            "lo.out_storage_time",
            "lo.materiel_type_id",
            "customer_name",
            "final_money",
            DB::raw('final_money-SUM(expend) as debt')
        )
            ->where("lo.main_id",$mainId)
            ->where("lo.all_id_type",$data['type'])
            ->where("lo.is_cancel",0)
            ->where("lo.delete_flag",0)
            ->where("lo.materiel_type_id",$data['goods_id'])
            ->groupBy("lo.all_id");

        //出售销售时间
        if($startTime != ''){
            $query->where("lo.out_storage_time",">=",$startTime);
        }
        if($endTime != ''){
            $query->where("lo.out_storage_time","<",$endTime);
        }

        //销售价排序
        if($sellNumSort == 'asc'){
            $query->orderBy("operate_num","asc");
        }elseif ($sellNumSort == 'desc'){
            $query->orderBy("operate_num","desc");
        }
        //默认出库时间倒序
        $query->orderBy("lo.out_storage_time","desc");
        if($export){
            //导出，获取所有
            $materielList = $query->get();
            if(!empty($materielList)){
                $sellList = $materielList->toArray();
            }
        }else{
            $materielList = $query->paginate($data['pageSize'],['*'], '',$data['currentPage']);
            if(!empty($materielList->toArray()['data'])){
                $sellList = $materielList->toArray()['data'];
            }
        }
        if(!empty($sellList)){
            foreach ($sellList as &$mVal){
                if(is_null($mVal['debt'])){
                    $mVal['debt'] = $mVal['final_money'];
                }elseif(is_numeric($mVal['debt']) && $mVal['debt']<= 0){
                    $mVal['debt'] = 0;
                }
            }

            if(!empty($sellDebtSort)){//数量排序
                $paramSort = array_column($sellList,'debt');
                $sort = $sellDebtSort=='desc'?SORT_DESC:SORT_ASC;
                array_multisort($paramSort,$sort,$sellList);
            }
        }
        $materielList = $materielList->toArray();
        if(isset($materielList['data'])){
            $materielList['data'] = $sellList;
        }else{
            $materielList= $sellList;
        }
        //返回结果
        $result['pageData'] = $materielList;

        if($export){
            return $result;
        }else{
            return CommonService::success('查询成功',$result);
        }
    }
    /**
     * 根据物料信息，查询销售报表
     * @param $data
     * @return void
     */
    public static function sellGoodsStatementList($mainId, $data, $export = false)
    {
        $result = ['pageData' => [], 'totalData' => []]; //返回结果
        $sellList = [];
        //第二步，从log表里分页查询出结果集
        $query = SellGoodsDetail::query()
            ->select(
                'b.materiel_name',
                'b.type_name',
                'b.materiel_no',
                'b.materiel_img',
                'cost as rt_cost',
                'cost_price as re_cost',
                'a.num',
            )->from("sell_goods_detail as a")
            ->leftJoin("materiel_type as b", 'b.id', '=', 'a.materiel_type_id')
            ->where([
                'sell_id' => $data['sell_id'],
                'repeal_status' => 0,
                'b.delete_flag' => 0
            ]);
        //第三步，计算统计总数
        $totalList = SellGoodsDetail::query()
            ->select([
                DB::raw("SUM(s_c.cost * s_a.num ) AS rt_total_cost_price"),
                DB::raw("SUM(s_a.cost_price * s_a.num) AS re_total_cost_price"),
            ])
            ->from('sell_goods_detail as a')
            ->leftJoin("materiel_type as c", "a.materiel_type_id", "=", "c.id")
            ->where([
                "a.repeal_status" => 0,
                "c.delete_flag" => 0,
                'a.sell_id' => $data['sell_id']
            ])->first();
        if (!empty($totalList)) {
            $arr = [
                'rt_total_cost_price',
                're_total_cost_price',
            ];
            foreach ($arr as $val) {
                if (empty($totalList[$val])) $totalList[$val] = 0;
            }
        }
        if ($export) {
            //导出，获取所有
            $materielList = $query->get();
            if (!empty($materielList)) {
                $sellList = $materielList->toArray();
            }
        } else {
            $materielList = $query->paginate($data['pageSize'], ['*'], '', $data['currentPage']);
            if (!empty($materielList->toArray()['data'])) {
                $sellList = $materielList->toArray()['data'];
            }
        }
        $materielList = $materielList->toArray();
        if (isset($materielList['data'])) {
            $materielList['data'] = $sellList;
        } else {
            $materielList = $sellList;
        }
        //返回结果
        $result['pageData'] = !empty($materielList) ? $materielList : [];
        $result['totalData'] = !empty($totalList->toArray()) ? $totalList->toArray() : [];
        if ($export) {
            return $result;
        } else {
            return CommonService::success('查询成功', $result);
        }
    }

    /**
     * 采购报表(新)
     * @param $data
     * @return void
     */
    public static function purchaseStatement($mainId, $data, $export = false)
    {
        $result = ['pageData' => [], 'totalData' => []]; //返回结果
        $startTime = isset($data['start_time']) ? self::dateToTime($data['start_time']) : ''; //开始时间
        $endTime = isset($data['end_time']) ? self::dateToTime($data['end_time'], 1) : ''; //结束时间 + 1天
        $materielNumSort = isset($data['materiel_num_sort']) ? strtolower($data['materiel_num_sort']) : ''; //采购数量排序，asc=升序，desc=降序，默认为空
        $materielDebtSortSort = isset($data['materiel_debt_sort']) ? strtolower($data['materiel_debt_sort']) : ''; //采购数量排序，asc=升序，desc=降序，默认为空
        $materielName = isset($data['materiel_name']) ? $data['materiel_name'] : ''; //物料名称
        $sellList = [];
        $totalList = [];
        $param = [//一级采购报表需要的参数
            "lo.all_id as id",
            "mt.materiel_name",
            "mt.type_name",
            "mt.materiel_no",
            "lo.materiel_type_id",
            DB::raw('SUM(operate_num) as purchase_num'),
            "mt.cost",
        ];
        if (!empty($data['goods_id'])) {//二级采购报表需要的参数
            $param = [
                "lo.all_id as id",
                "s.purchase_no as order_no",
                "lo.operate_num as purchase_num",
                "lo.materiel_mean_cost",
                "s.purchase_time",
                "lo.materiel_type_id",
                "sc.name as customer_name",
                "final_money",
                DB::raw('final_money-SUM(money) as debt')
            ];
        }
        //第二步，从log表里分页查询出结果集
        $query = LogInStorageModel::query();
        $query->from('log_instorage AS lo');
        if (!empty($data['goods_id'])) {//-根据商品信息查询二级采购报表
            $query->leftJoin("materiel AS s", "lo.all_id", "=", "s.id");
            $query->leftJoin("supplier_customer AS sc", "sc.id", "=", "s.supplier_customer_id");
            $query->leftJoin('materiel_pay_history as sp', 'sp.materiel_id', '=', 's.id');
            $query->where("lo.main_id", $mainId);
            $query->where("lo.materiel_type_id", $data['goods_id']);
            $query->where("s.delete_flag", 0);
            $query->groupBy("s.purchase_no");
        } else {//一级采购报表
            $query->leftJoin("materiel_type AS mt", "lo.materiel_type_id", "=", "mt.id");
            $query->groupBy("lo.materiel_type_id");
            if (!empty($data['user_id'])) {
                $query->where("lo.user_id", $data['user_id']);
            } else {
                $query->where("lo.main_id", $mainId);
            }
            //搜索物料名称

            if ($materielName != '') {
                //从物料表里查 id 集合
                $query->where('mt.materiel_name', 'like', '%' . $materielName . '%');
            }
            $queryTotal = LogInStorageModel::query();
            $queryTotal->from('log_instorage AS lo')
                ->leftJoin("materiel_type AS mt", "lo.materiel_type_id", "=", "mt.id")
                ->select(DB::raw("SUM(cost * operate_num ) AS purchase_total_price"));
            if (!empty($data['user_id'])) {
                $queryTotal->where("lo.user_id", $data['user_id']);
            } else {
                $queryTotal->where("lo.main_id", $mainId);
            }
            $queryTotal->where("lo.all_id_type", 3)
                ->where("lo.is_cancel", 0)
                ->where("lo.delete_flag", 0)
                ->where("mt.delete_flag", 0);
            if ($materielName != '') {
                //从物料表里查 id 集合
                $queryTotal->where('mt.materiel_name', 'like', '%' . $materielName . '%');
            }
            if ($startTime != '') {
                $queryTotal->where("lo.in_storage_time", ">=", $startTime);
            }
            if ($endTime != '') {
                $queryTotal->where("lo.in_storage_time", "<", $endTime);
            }
            $totalList = $queryTotal->first();
        }
        $query->select($param)
            ->where("lo.all_id_type", 3)
            ->where("lo.is_cancel", 0)
            ->where("lo.delete_flag", 0);
        //采购时间
        if ($startTime != '') {
            $query->where("lo.in_storage_time", ">=", $startTime);
        }
        if ($endTime != '') {
            $query->where("lo.in_storage_time", "<", $endTime);
        }

        //采购数量排序
        if ($materielNumSort == 'asc') {
            $query->orderBy("debt", "asc");
        } elseif ($materielNumSort == 'desc') {
            $query->orderBy("debt", "desc");
        }

        //采购数量排序
        if ($materielNumSort == 'asc') {
            $query->orderBy("purchase_num", "asc");
        } elseif ($materielNumSort == 'desc') {
            $query->orderBy("purchase_num", "desc");
        }
        //默认入库时间倒序
        $query->orderBy("lo.in_storage_time", "desc");
        if ($export) {
            //导出，获取所有
            $materielList = $query->get();
            if (!empty($materielList)) {
                $sellList = $materielList->toArray();
            }
        } else {
            $materielList = $query->paginate($data['pageSize'], ['*'], '', $data['currentPage']);
            if (!empty($materielList->toArray()['data'])) {
                $sellList = $materielList->toArray()['data'];
            }
        }
        if (!empty($sellList) && !empty($data['goods_id'])) {
            foreach ($sellList as &$mVal) {
                if (is_null($mVal['debt'])) {
                    $mVal['debt'] = $mVal['final_money'];
                } elseif (is_numeric($mVal['debt']) && $mVal['debt'] <= 0) {
                    $mVal['debt'] = 0;
                }

                if (!empty($mVal['purchase_time'])) $mVal['purchase_time'] = date('Y-m-d H:i:s', $mVal['purchase_time']);
            }
        }
        $materielList = $materielList->toArray();
        if (isset($materielList['data'])) {
            $materielList['data'] = $sellList;
        } else {
            $materielList = $sellList;
        }
        //返回结果
        $result['pageData'] = $materielList;
        $result['totalData'] = !empty($totalList) ? $totalList->toArray() : [];
        if ($export) {
            return $result;
        } else {
            return CommonService::success('查询成功', $result);
        }
    }

    /**
     * 把日期转换为时间戳
     */
    public static function dateToTime($dateTime, $addDay = '')
    {
        if ($dateTime == '') {
            return '';
        }

        if ($addDay != '') {
            return strtotime("{$dateTime} +{$addDay} day");
        }

        return strtotime($dateTime);
    }

    /**
     *设置订单欠款提醒时间
     * Author: zlf
     * Created at 2024/2/22 11:23
     * @param $data
     * @return array
     */
    public static function setDebtReminderTime($data)
    {
        $model = "";
        switch ($data['type']) {
            case 1://销售
                $model = SellModel::query();
                break;
            case 2://换货
                $model = BarterModel::query();
                break;
            case 3://采购
                $model = MaterielModel::query();
                break;
        }
        $bool = $model->where([
            'id' => $data['id'],
            'main_id' => $data['main_id']
        ])->update(['debt_reminder_time' => strtotime($data['debt_reminder_time'])]);
        if ($bool) {
            return ['code' => 0, 'msg' => 'success', 'data' => ''];
        } else {
            return ['code' => 1, 'msg' => '欠款提醒时间，设置有误！', 'data' => ''];
        }
    }

    /**
     * 统计销售报表
     * @param $data
     * @return void
     */
    public static function sellStatementList($mainId, $data, $export = false)
    {
        $result = ['pageData' => [], 'totalData' => []]; //返回结果
        $startTime = isset($data['start_time']) ? self::dateToTime($data['start_time']) : ''; //开始时间
        $endTime = isset($data['end_time']) ? self::dateToTime($data['end_time'], 1) : ''; //结束时间 + 1天
        $query = SellGoodsDetail::query()
            ->select([
                "b.id as sell_id",
                "b.order_no",
                "b.sell_date as out_storage_time",
                "d.name as customer_name",
                "a.price as sell_price",
                "a.cost_price as materiel_mean_cost",
                "c.cost",
                "a.num",
                "a.materiel_type_id",
                "b.final_money as re_sell_price",
                "b.customer_id",
                "c.materiel_name",
                "c.type_name",
                "c.materiel_no",
                DB::raw("SUM(s_a.cost_price * s_a.num ) AS re_cost_price"),
                DB::raw("SUM(s_c.cost * s_a.num ) AS rt_cost_price"),
            ])
            ->from('sell_goods_detail as a')
            ->leftJoin('sell as b', 'b.id', '=', 'a.sell_id')
            ->leftJoin("materiel_type as c", "a.materiel_type_id", "=", "c.id")
            ->leftJoin("supplier_customer as d", "d.id", "=", "b.customer_id")
            ->where([
                "b.repeal_status" => 0,
                "a.repeal_status" => 0,
                "b.delete_flag" => 0,
                "c.delete_flag" => 0,
                "d.delete_flag" => 0,
            ])->where(function ($query) use ($data, $mainId, $startTime, $endTime) {
                if (!empty($data['user_id'])) {
                    $query->where("b.user_id", $data['user_id']);
                } else {
                    $query->where("b.main_id", $mainId);
                }
                if ($startTime !== '') {
                    $query->where("b.sell_date", ">=", $startTime);
                }
                if ($endTime !== '') {
                    $query->where("b.sell_date", "<", $endTime);
                }
                if (!empty($data['materiel_name'])) {
                    if (preg_match('/^[\x{4e00}-\x{9fa5}]+$/u', $data['materiel_name']) === 1) {
                        $query->where("d.name", "like", '%' . $data['materiel_name'] . '%');
                    } else {
                        $query->where("b.order_no", "like", '%' . $data['materiel_name'] . '%');
                    }
                }
                if (!empty($data['customer_id'])) {
                    $query->where("b.customer_id", $data['customer_id']);
                }
            })->orderBy("b.sell_date", "desc")
            ->groupBy("b.id");
        if ($export) {
            //导出，获取所有
            $materielList = $query->get();
        } else {
            $materielList = $query->paginate($data['pageSize'], ['*'], '', $data['currentPage']);
        }
        //第三步，计算统计总数
        $totalList = SellGoodsDetail::query()
            ->select([
                DB::raw("SUM(s_c.selling_price * s_a.num) AS rt_total_sell_price"),
                DB::raw("SUM(s_c.cost * s_a.num ) AS rt_total_cost_price"),
                DB::raw("SUM(s_a.price*s_a.num) AS re_total_sell_price"),
                DB::raw("SUM(s_a.cost_price * s_a.num) AS re_total_cost_price"),
            ])
            ->from('sell_goods_detail as a')
            ->leftJoin('sell as b', 'b.id', '=', 'a.sell_id')
            ->leftJoin("materiel_type as c", "a.materiel_type_id", "=", "c.id")
            ->leftJoin("supplier_customer as d", "d.id", "=", "b.customer_id")
            ->where([
                "b.repeal_status" => 0,
                "a.repeal_status" => 0,
                "b.delete_flag" => 0,
                "c.delete_flag" => 0,
                "d.delete_flag" => 0,
            ])->where(function ($query) use ($data, $mainId, $startTime, $endTime) {
                if (!empty($data['user_id'])) {
                    $query->where("b.user_id", $data['user_id']);
                } else {
                    $query->where("b.main_id", $mainId);
                }
                if ($startTime !== '') {
                    $query->where("b.sell_date", ">=", $startTime);
                }
                if ($endTime !== '') {
                    $query->where("b.sell_date", "<", $endTime);
                }
                if (!empty($data['materiel_name'])) {
                    if (preg_match('/^[\x{4e00}-\x{9fa5}]+$/u', $data['materiel_name']) === 1) {
                        $query->where("d.name", "like", '%' . $data['materiel_name'] . '%');
                    } else {
                        $query->where("b.order_no", "like", '%' . $data['materiel_name'] . '%');
                    }
                }
                if (!empty($data['customer_id'])) {
                    $query->where("b.customer_id", $data['customer_id']);
                }
            })->first();

        if (!empty($totalList)) {
            $totalList['rt_total_profit'] = $totalList['re_total_sell_price'] - $totalList['rt_total_cost_price'];
            $totalList['re_total_profit'] = $totalList['re_total_sell_price'] - $totalList['re_total_cost_price'];
            $arr = [
                'rt_total_sell_price',
                'rt_total_cost_price',
                're_total_sell_price',
                're_total_cost_price',
                'rt_total_profit',
                're_total_profit'
            ];
            foreach ($arr as $val) {
                if (empty($totalList[$val])) $totalList[$val] = 0;
            }
        }
        //返回结果
        $result['pageData'] = !empty($materielList->toArray()) ? $materielList->toArray() : [];
        $result['totalData'] = !empty($totalList->toArray()) ? $totalList->toArray() : [];
        if ($export) {
            return $result;
        } else {
            return CommonService::success('查询成功', $result);
        }
    }

}
